import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import java.util.concurrent.Callable;

public class Test {
    //1. 新增貂蝉同学的借阅记录：诗经，从2019年9月25日17:50到2019年10月25日17:50
    public static void main1(String[] args) throws SQLException{
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("cxh0805");
        Connection connection=dataSource.getConnection();
    }
    //4. 删除id最大的一条借阅记录
    public static void main4(String[] args) throws  SQLException{
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("cxh0805");
       Connection connection=dataSource.getConnection();
        String sql = "delete from borrow_info where id =" +
                "(select r.id from (select max(id) id from borrow_info) r)";
     PreparedStatement statement= connection.prepareStatement(sql);
     int row = statement.executeUpdate();
     statement.close();
     connection.close();
    }
    //3. 修改图书《深入理解Java虚拟机》的价格为61.20
    public static void main3(String[] args) throws  SQLException{
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("cxh0805");
        Connection connection= dataSource.getConnection();
        String sql="update book set price=61.20 where name='深入理解Java虚拟机'";
        PreparedStatement statement= connection.prepareStatement(sql);
        int row= statement.executeUpdate();
        statement.close();
        connection.close();
    }


    //查询计算机的借阅
    public static void main2(String[] args) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("cxh0805");
        //连接数据库
        Connection connection = dataSource.getConnection();
        String sql = "select category.name,student.name,book.name, borrow_info.start_time, borrow_info.end_time " +
                "from borrow_info join book on book.id=borrow_info.book_id " +
                "join category on category.id=book.category_id " +
                "join student on borrow_info.student_id=student.id " +
                "where category.name=?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, "计算机");
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
            String categoryName = resultSet.getString("category.name");
            String bookName = resultSet.getString("book.name");
            String studentName = resultSet.getString("student.name");
            String startTime = resultSet.getString("start_time");
            String endTime = resultSet.getString("end_time");
            System.out.println("书的类型:" + categoryName + " " + "书名:" + bookName + " 借阅人的姓名:" + studentName+
                    " 借阅时间:" + startTime + " 还书时间:" + endTime);
        }
        statement.close();
        connection.close();
    }
    public static void main (String[] args) throws SQLException {
        //创建dataSource
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("cxh0805");
        //连接数据库
        Connection connection = dataSource.getConnection();
        //通过控制台。来进行插入
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入学号");
        int id = scanner.nextInt();
        System.out.println("请输入姓名");
        String name = scanner.next();
        String sql = "insert into student values(?,?)";
        PreparedStatement Statement = connection.prepareStatement(sql);
        Statement.setInt(1, id);
        Statement.setString(2, name);
        int row1 = Statement.executeUpdate();
        Statement.close();
        connection.close();
    /*
        //删除
        String sql1="delete from student where id=2";
        PreparedStatement statement= connection.prepareStatement(sql1);
        int row2=statement.executeUpdate();
        statement.close();
        //改
        String sql2="update student set name='tiantian' where id=1";
        PreparedStatement statement1= connection.prepareStatement(sql2);
        int row=statement1.executeUpdate();
        statement1.close();
        //查
        String sql3="select *from student";
        PreparedStatement statement2= connection.prepareStatement(sql3);
        //结果集
        ResultSet resultSet=statement2.executeQuery();
        while(resultSet.next()){
          int id1=resultSet.getInt("id");
          String na=resultSet.getString("name");
            System.out.println(id1+" "+na);
        }
        statement2.close();
    }
    }
     */
    }
}
